package zozopo.warehouse.operations;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import zozopo.database.BusiDatabaseManager;
import zozopo.database.SqlMachine;
import zozopo.operations.comm.OperatingService;

public class PurchaselistOS  extends OperatingService{
	
	
	public PurchaselistOS(Connection conn){
		super(conn);
	}
	
	public PurchaselistOS(){
		this.databaseConnection = new BusiDatabaseManager();
		this.conn = databaseConnection.getConnection();
	}
	
	/**
	 * @version 1.0
	 * @see 
	 */
	public List<PurchaselistTB> selectListBySql(String sql){
		List<PurchaselistTB> list = null;
		ResultSet resultSet = databaseConnection.executeQuery(conn, sql);
		//Flow flow = new Flow(conn);
		//Map flowMap = flow.getFlowStateMapByKind(FlowCode.HOUSEWARE_RECEIPTLIST);
		if(resultSet != null){
				try {
					list = new ArrayList<PurchaselistTB>();
					while(resultSet.next()){
						PurchaselistTB obj = new PurchaselistTB();
						
						obj.setId_purchaselist(resultSet.getInt(PurchaselistTB.ID_PURCHASELIST));
						
						obj.setDate_purchaselist(resultSet.getString(PurchaselistTB.DATE_PURCHASELIST));
						obj.setDepartment_purchaselist(resultSet.getString(PurchaselistTB.DEPARTMENT_PURCHASELIST));
						obj.setDepartmentid_purchaselist(resultSet.getInt(PurchaselistTB.DEPARTMENTID_PURCHASELIST));
						obj.setGoodsnumber_purchaselist(resultSet.getInt(PurchaselistTB.GOODSNUMBER_PURCHASELIST));
						obj.setRemark_purchaselist(resultSet.getString(PurchaselistTB.REMARK_PURCHASELIST));
						obj.setShipprice_purchaselist(resultSet.getDouble(PurchaselistTB.SHIPPRICE_PURCHASELIST));
						
						
						obj.setSupplier_purchaselist(resultSet.getString(PurchaselistTB.SUPPLIER_PURCHASELIST));
						obj.setSupplierinfo_purchaselist(resultSet.getString(PurchaselistTB.SUPPLIERINFO_PURCHASELIST));
						obj.setTotal_purchaselist(resultSet.getDouble(PurchaselistTB.TOTAL_PURCHASELIST));
						obj.setUseremail_purchaselist(resultSet.getString(PurchaselistTB.USEREMAIL_PURCHASELIST));
						obj.setUsername_purchaselist(resultSet.getString(PurchaselistTB.USERNAME_PURCHASELIST));
						
						obj.setUnlockemail_purchaselist(resultSet.getString(PurchaselistTB.UNLOCKEMAIL_PURCHASELIST));
						obj.setUnlockname_purchaselist(resultSet.getString(PurchaselistTB.UNLOCKNAME_PURCHASELIST));
						
						obj.setIsconfirm_purchaselist(resultSet.getBoolean(PurchaselistTB.ISCONFIRM_PURCHASELIST));
						obj.setIscancel_purchaselist(resultSet.getBoolean(PurchaselistTB.ISCANCEL_PURCHASELIST));
						
						list.add(obj);
					}
					resultSet.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}
		
		return list;
	
	}
	
	/**
	 * @version 1.0
	 * @see 
	 */
	public boolean insert(PurchaselistTB obj){
		SqlMachine sqlMachine = new SqlMachine(conn);
		HashMap<String, Object> insertMap = new HashMap<String, Object>();
		
		insertMap.put(PurchaselistTB.GOODSNUMBER_PURCHASELIST, obj.getGoodsnumber_purchaselist());
		insertMap.put(PurchaselistTB.REMARK_PURCHASELIST, obj.getRemark_purchaselist());
		insertMap.put(PurchaselistTB.SHIPPRICE_PURCHASELIST, obj.getShipprice_purchaselist());
		insertMap.put(PurchaselistTB.SUPPLIER_PURCHASELIST, obj.getSupplier_purchaselist());
		insertMap.put(PurchaselistTB.SUPPLIERINFO_PURCHASELIST, obj.getSupplierinfo_purchaselist());
		insertMap.put(PurchaselistTB.TOTAL_PURCHASELIST, obj.getTotal_purchaselist());
		insertMap.put(PurchaselistTB.USEREMAIL_PURCHASELIST, obj.getUseremail_purchaselist());
		insertMap.put(PurchaselistTB.USERNAME_PURCHASELIST, obj.getUsername_purchaselist());
		
		
		return sqlMachine.insertRow(PurchaselistTB.TABLE_NAME, insertMap);
	}
	
	/**
	 * @version 1.0
	 * @see 
	 */
	public boolean insertAndLocked(PurchaselistTB obj){
		SqlMachine sqlMachine = new SqlMachine(conn);
		HashMap<String, Object> insertMap = new HashMap<String, Object>();
		
		insertMap.put(PurchaselistTB.GOODSNUMBER_PURCHASELIST, obj.getGoodsnumber_purchaselist());
		insertMap.put(PurchaselistTB.REMARK_PURCHASELIST, obj.getRemark_purchaselist());
		insertMap.put(PurchaselistTB.SHIPPRICE_PURCHASELIST, obj.getShipprice_purchaselist());
		insertMap.put(PurchaselistTB.SUPPLIER_PURCHASELIST, obj.getSupplier_purchaselist());
		insertMap.put(PurchaselistTB.SUPPLIERINFO_PURCHASELIST, obj.getSupplierinfo_purchaselist());
		insertMap.put(PurchaselistTB.TOTAL_PURCHASELIST, obj.getTotal_purchaselist());
		insertMap.put(PurchaselistTB.USEREMAIL_PURCHASELIST, obj.getUseremail_purchaselist());
		insertMap.put(PurchaselistTB.USERNAME_PURCHASELIST, obj.getUsername_purchaselist());
		insertMap.put(PurchaselistTB.ISCONFIRM_PURCHASELIST, false);   //locked
		
		return sqlMachine.insertRow(PurchaselistTB.TABLE_NAME, insertMap);
	}
	
	/**
	 * @version 1.0
	 * @author Shea
	 */
	public int getMaxIdByEmail(String email){
		//String sql = "select * from "+ReleaseTB.TABLE_NAME+" order by  "+ReleaseTB.RELEASE_DATE+" desc limit "+beginPageNum+","+pageSize; 
		SqlMachine sqlMachine = new SqlMachine(conn);
		int maxId = sqlMachine.getMaxId(PurchaselistTB.ID_PURCHASELIST, PurchaselistTB.TABLE_NAME, PurchaselistTB.USEREMAIL_PURCHASELIST, email);
		return maxId;
	}
	
	
	
	public PurchaselistTB getObjById(int id){
		String sql = "SELECT * FROM " + PurchaselistTB.TABLE_NAME + " where " + PurchaselistTB.ID_PURCHASELIST + "=" + id;
		PurchaselistTB obj = new PurchaselistTB();
		List<PurchaselistTB> list = selectListBySql(sql);
		
		if(list.size() < 1){//如果为空.
			return null;
		}else{               //否则
			obj = list.get(0);
			return obj;
		}
		
		
	}
	
	/**
	 * @version 1.0
	 * @see 
	 */
	public List<PurchaselistTB> select(){
		String sql = "SELECT * FROM " + PurchaselistTB.TABLE_NAME;
		List<PurchaselistTB> list =  selectListBySql(sql);
		
		return list;
	}
	
	public List<PurchaselistTB> select(int beginPageNum,int pageSize){
		
		List<PurchaselistTB> list = null;
		String sql = "SELECT * FROM " + PurchaselistTB.TABLE_NAME  
		+ " ORDER BY " + PurchaselistTB.ID_PURCHASELIST + " DESC "
		+ " limit "+beginPageNum+","+pageSize; 
		list = selectListBySql(sql);
		
		return list;
	}
	
	public List<AftersaleserviceTB> selectByReceiptlistid(int receiptlistid){
		
		List<AftersaleserviceTB> list = null;
		String sql = "SELECT * FROM " + AftersaleserviceTB.TABLE_NAME  
		+ " where " + AftersaleserviceTB.RECEIPTLISTID_AFTERSALESERVICE + "=" + receiptlistid
		
		+ " ORDER BY " + AftersaleserviceTB.ID_AFTERSALESERVICE + " DESC ";
		
		//list = selectListBySql(sql);
		
		return list;
	}
	
	/**
	 * @version 1.0
	 * @see 
	 */
	public int getRowNum(){
		
		int rowNum = 0;
		String sql = "select count(*) as rowNum from " + PurchaselistTB.TABLE_NAME ;
		
		ResultSet resultSet = databaseConnection.executeQuery(conn, sql);
		if(resultSet != null){
			try {
				
				while(resultSet.next()){
					rowNum = resultSet.getInt("rowNum");
				}
				resultSet.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return rowNum;
	}
	
	/**
	 * @version 1.0
	 * @author Shea
	 */
	public List<PurchaselistTB> selectBySearchStr(String searchStr,int beginPageNum,int pageSize){
		String sql = "select * from "+PurchaselistTB.TABLE_NAME+" WHERE  "
		+PurchaselistTB.ID_PURCHASELIST+" LIKE '%"+searchStr+"%'"
		+" OR "+PurchaselistTB.DATE_PURCHASELIST+" LIKE '%"+searchStr+"%'"
		+" OR "+PurchaselistTB.DEPARTMENT_PURCHASELIST+" LIKE '%"+searchStr+"%'"
		+" OR "+PurchaselistTB.DEPARTMENTID_PURCHASELIST+" LIKE '%"+searchStr+"%'"
		
		+" OR "+PurchaselistTB.REMARK_PURCHASELIST+" LIKE '%"+searchStr+"%'"
		+" OR "+PurchaselistTB.SUPPLIER_PURCHASELIST+" LIKE '%"+searchStr+"%'"
		+" OR "+PurchaselistTB.SUPPLIERINFO_PURCHASELIST+" LIKE '%"+searchStr+"%'"
		+" OR "+PurchaselistTB.USERNAME_PURCHASELIST+" LIKE '%"+searchStr+"%'"

		

		+ " ORDER BY " + PurchaselistTB.DATE_PURCHASELIST + " DESC "
		
		+" limit "+beginPageNum+","+pageSize; 
		//ResultSet resultSet = databaseConnection.executeQuery(conn, sql);
		List<PurchaselistTB> list = selectListBySql(sql);
		
		return list;
	}
	/**
	 * @version 1.0
	 * @see 
	 */
	public int getRowNumForSearchStr(String searchStr){
		
		int rowNum = 0;
		String sql = "select count(*) as rowNum from " + PurchaselistTB.TABLE_NAME +" WHERE  "
		+PurchaselistTB.ID_PURCHASELIST+" LIKE '%"+searchStr+"%'"
		+" OR "+PurchaselistTB.DATE_PURCHASELIST+" LIKE '%"+searchStr+"%'"
		+" OR "+PurchaselistTB.DEPARTMENT_PURCHASELIST+" LIKE '%"+searchStr+"%'"
		+" OR "+PurchaselistTB.DEPARTMENTID_PURCHASELIST+" LIKE '%"+searchStr+"%'"
		
		+" OR "+PurchaselistTB.REMARK_PURCHASELIST+" LIKE '%"+searchStr+"%'"
		+" OR "+PurchaselistTB.SUPPLIER_PURCHASELIST+" LIKE '%"+searchStr+"%'"
		+" OR "+PurchaselistTB.SUPPLIERINFO_PURCHASELIST+" LIKE '%"+searchStr+"%'"
		+" OR "+PurchaselistTB.USERNAME_PURCHASELIST+" LIKE '%"+searchStr+"%'";
		
		ResultSet resultSet = databaseConnection.executeQuery(conn, sql);
		if(resultSet != null){
			try {
				
				while(resultSet.next()){
					rowNum = resultSet.getInt("rowNum");
				}
				resultSet.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return rowNum;
	}
	
	
	
	
	
	
	
	public boolean cancle(int id){
		
			
			SqlMachine sqlMachine = new SqlMachine(conn);
			HashMap<String, Object> updateMap = new HashMap<String, Object>();
			
			updateMap.put(PurchaselistTB.ISCANCEL_PURCHASELIST, true);
						
			return sqlMachine.updateRow(PurchaselistTB.TABLE_NAME, updateMap, PurchaselistTB.ID_PURCHASELIST, id);
			
			
	}
	
	public boolean confirmPurchaselist(PurchaselistTB purchaselist){
		SqlMachine sqlMachine = new SqlMachine(conn);
		HashMap<String, Object> updateMap = new HashMap<String, Object>();
		
		
		
		updateMap.put(PurchaselistTB.ISCONFIRM_PURCHASELIST, true);
		
		updateMap.put(PurchaselistTB.UNLOCKEMAIL_PURCHASELIST, purchaselist.getUnlockemail_purchaselist());
		updateMap.put(PurchaselistTB.UNLOCKNAME_PURCHASELIST, purchaselist.getUnlockname_purchaselist());
		
		return sqlMachine.updateRow(PurchaselistTB.TABLE_NAME, updateMap, PurchaselistTB.ID_PURCHASELIST, purchaselist.getId_purchaselist());
	}
	
	public boolean unconfirmPurchaselist(PurchaselistTB purchaselist){
		SqlMachine sqlMachine = new SqlMachine(conn);
		HashMap<String, Object> updateMap = new HashMap<String, Object>();
		
		updateMap.put(PurchaselistTB.ISCONFIRM_PURCHASELIST, false);
		//updateMap.put(PurchaselistTB.STATE_PURCHASELIST, ZOZOPOCODE.LOCKED);
	
		return sqlMachine.updateRow(PurchaselistTB.TABLE_NAME, updateMap, PurchaselistTB.ID_PURCHASELIST, purchaselist.getId_purchaselist());
	}
	
	/**
	 * @version 1.0
	 * @author Shea
	 */
	public int getTodayCountNum(){
		
		int num = 0;
		String sql = "select COUNT(*) as num from " + PurchaselistTB.TABLE_NAME 
		+ " WHERE "
	 + PurchaselistTB.ISCANCEL_PURCHASELIST + "=false"
		//+ " limit "+beginPageNum+","+pageSize 
		
		+ " AND TO_DAYS( " + PurchaselistTB.DATE_PURCHASELIST +") = TO_DAYS(now())";
		
		ResultSet resultSet = databaseConnection.executeQuery(conn, sql);
		if(resultSet != null){
			try {
				
				while(resultSet.next()){
					num = resultSet.getInt("num");
				}
				resultSet.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return num;
	}
	
	/**
	 * @version 1.0
	 * @author Shea
	 */
	public double getTodayTotal(){
		
		double num = 0;
		String sql = "select SUM("+PurchaselistTB.TOTAL_PURCHASELIST+") as num from " + PurchaselistTB.TABLE_NAME 
		+ " WHERE " + PurchaselistTB.ISCANCEL_PURCHASELIST  + "=false"
		
		+ " AND TO_DAYS( " + PurchaselistTB.DATE_PURCHASELIST +") = TO_DAYS(now())";
		
		ResultSet resultSet = databaseConnection.executeQuery(conn, sql);
		if(resultSet != null){
			try {
				
				while(resultSet.next()){
					num = resultSet.getDouble("num");
				}
				resultSet.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return num;
	}
	
	
	/**
	 * @version 1.0
	 * @author Shea
	 */
	public List<PurchaselistTB> selectByFlowCode(String flowCode,String kindCode,int beginPageNum,int pageSize){
		String sql = "SELECT * FROM "+PurchaselistTB.TABLE_NAME+" LEFT JOIN recordlist_flow " +
				" ON recordlist_flow.kindid_recordlist = " + PurchaselistTB.ID_PURCHASELIST +
				" AND recordlist_flow.flowflag_recordlist='1' " +
				" WHERE  recordlist_flow.code_recordlist='"+flowCode+"'" +
				" AND recordlist_flow.kind_recordlist='"+kindCode+"' " +
				" AND " + PurchaselistTB.ISCANCEL_PURCHASELIST+ "=false " +
		        " limit "+beginPageNum+","+pageSize; 
		//ResultSet resultSet = databaseConnection.executeQuery(conn, sql);
		List<PurchaselistTB> receiptlistList = selectListBySql(sql);
		return receiptlistList;
	}
	
	/**
	 * @version 1.0
	 * @see 
	 */
	public int getRowNumForFlowCode(String flowCode,String kindCode){
		
		int rowNum = 0;
		
		String sql = "SELECT count(*) as rowNum FROM "+PurchaselistTB.TABLE_NAME+" LEFT JOIN recordlist_flow " +
					" ON recordlist_flow.kindid_recordlist = "+ PurchaselistTB.ID_PURCHASELIST +
					" AND recordlist_flow.flowflag_recordlist='1' " +
					" AND recordlist_flow.kind_recordlist='"+kindCode+"' " +
					" WHERE  recordlist_flow.code_recordlist='"+flowCode+"'"+
					" AND " + PurchaselistTB.ISCANCEL_PURCHASELIST+ "=false ";
		
		ResultSet resultSet = databaseConnection.executeQuery(conn, sql);
		if(resultSet != null){
			try {
				
				while(resultSet.next()){
					rowNum = resultSet.getInt("rowNum");
				}
				resultSet.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return rowNum;
	}
	
	/**
	 * @version 1.0
	 * @author gee
	 */
	public Map<String,Double> getTotalMap(){
		
		String sql = "select DATE_FORMAT("+PurchaselistTB.DATE_PURCHASELIST+",'%Y-%m') AS date_purchaselist,SUM("+PurchaselistTB.TOTAL_PURCHASELIST+") as num from " + PurchaselistTB.TABLE_NAME 
		+ " WHERE " 
		+ PurchaselistTB.ISCANCEL_PURCHASELIST + "=false "
		
		+ " GROUP BY DATE_FORMAT( " + PurchaselistTB.DATE_PURCHASELIST +",'%Y-%m')";
		
	
		ResultSet resultSet = databaseConnection.executeQuery(conn, sql);
		//Flow flow = new Flow(conn);
		Map<String,Double> map = null;
		
		if(resultSet != null){
				try {
					map = new LinkedHashMap<String,Double>();
					while(resultSet.next()){
						map.put(resultSet.getString((PurchaselistTB.DATE_PURCHASELIST)), resultSet.getDouble("num"));
					}
					resultSet.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}
		
		return map;
	}
}
